﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;

namespace WebTree.ZTree
{
    public static class SqlHelper
    {
        private static string _strConn = "Data Source=.;Initial Catalog=TREEDB;User ID=dev;Password=dev";

        public static DataTable Query(string strSql, SqlParameter[] parms)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(_strConn))
            {
                try
                {

                    conn.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(strSql, conn))
                    {
                        if (parms != null)
                        {
                            da.SelectCommand.Parameters.AddRange(parms);
                        }

                        da.Fill(dt);
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }



            }

            return dt;
        }

        public static bool Execute(string strSql, SqlParameter[] parms)
        {

            using (SqlConnection conn = new SqlConnection(_strConn))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(strSql, conn))
                    {
                        if (parms != null)
                        {
                            cmd.Parameters.AddRange(parms);
                        }
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }

        }


        public static List<T> ToEntities<T>(DataTable dt)/*必须来在于数据库来自于文件可能存在问题*/ where T : class,new()
        {
            //IsNullable
            if (null == dt || dt.Rows.Count == 0)
                return null;
            //Initial Entities
            List<T> entities = new List<T>();
            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    PropertyInfo[] pArray = typeof(T).GetProperties();
                    T entity = new T();

                    foreach (PropertyInfo p in pArray)
                    {
                        if (dt.Columns.Contains(p.Name))
                        {
                            if (row[p.Name] != DBNull.Value)
                            {
                                p.SetValue(entity, row[p.Name], null);
                            }
                        }

                    }
                    //Array.ForEach<PropertyInfo>(pArray, p =>
                    //{
                    //    if (dt.Columns.Contains(p.Name))
                    //    {
                    //        if (row[p.Name] != DBNull.Value)
                    //        {
                    //            p.SetValue(entity, row[p.Name].ToString(), null);
                    //        }
                    //    }
                    //});
                    entities.Add(entity);
                }
                return entities;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}